﻿// =================================================================== 
// 项目说明
//====================================================================
// lsc @Copy Right 2019
// 文件： CluesRepository.cs
// 项目名称：斑马车务
// 创建时间：2019-12-11
// 负责人：lsc
// ===================================================================
using System;
using System.Text;
using System.Collections.Generic;
using CRM.Infrastructure.Entity;
using Dapper.Extensions;
using CRM.Infrastructure.Models;
using CRM.Infrastructure.Dto;
using Dapper;
using CRM.Utility;

namespace CRM.Infrastructure.Repository
{
    /// <summary>
    /// 仓储层
    /// </summary>	
    public class CluesRepository : BaseRepository<Clues>
    {

        #region 构造

        public CluesRepository() : base() { }

        public CluesRepository(string connectionString) : base(connectionString) { }

        public CluesRepository(DatabaseContext dbContext) : base(dbContext) { }

        #endregion

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public Page<CluesDto> GetPages(ClueSearchModel model)
        {
            StringBuilder sb = new StringBuilder("SELECT T.*,T2.UserName as SeatName FROM Clues T WITH(NOLOCK)  ");
            sb.Append(" LEFT JOIN [Users] T2 WITH(NOLOCK) on T.UserID=  T2.UserID");
            sb.Append(" WHERE T.IsActive=1");
            var dparams = new DynamicParameters();
            AddParams(model, sb, dparams);
            return dbContext.Connection.Page<CluesDto>(model.PageIndex, model.PageSize, sb.ToString(), dparams);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public Page<CluesDto> GetPCPages(ClueSearchPCModel model)
        {
            StringBuilder sb = new StringBuilder("SELECT T.*,T2.UserName as SeatName FROM Clues T WITH(NOLOCK)  ");
            sb.Append(" LEFT JOIN [Users] T2 WITH(NOLOCK) on T.UserID=  T2.UserID");
            sb.Append(" WHERE T.IsActive=1");
            var dparams = new DynamicParameters();
            AddPCParams(model, sb, dparams);
            return dbContext.Connection.Page<CluesDto>(model.PageIndex, model.PageSize, sb.ToString(), dparams);
        }

        /// <summary>
        /// 获取同步信息
        /// </summary>
        /// <param name="companyID"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List<CluesDto> GetSync(int companyID, int pageSize)
        {
            StringBuilder sb = new StringBuilder(string.Format("SELECT TOP {0} T.* FROM Clues T WITH(NOLOCK)", pageSize));
            sb.Append(" WHERE T.IsActive=1 AND T.IsSync=0 AND T.CompanyID=" + companyID);
            sb.Append(" ORDER BY T.SortNum ASC");
            return dbContext.Connection.Query<CluesDto>(sb.ToString()).AsList();
        }

        /// <summary>
        /// 更新已经同步
        /// </summary>
        /// <param name="clueIds"></param>
        /// <returns></returns>
        public int UpdateSync(List<CluesDto> clues)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var item in clues)
            {
                sb.Append("," + item.ClueID);
            }
            string sql = string.Format("UPDATE Clues SET IsSync=1 WHERE ClueID IN ({0})", sb.ToString(1, sb.Length - 1));
            return Execute(sql);
        }

        /// <summary>
        /// 构建查询条件
        /// </summary>
        /// <param name="model"></param>
        /// <param name="sb"></param>
        /// <param name="dparams"></param>
        private void AddPCParams(ClueSearchPCModel model, StringBuilder sb, DynamicParameters dparams)
        {
            if (model.UserID > -1)
            {
                sb.Append(" AND T.UserID=@UserID");
                dparams.Add("UserID", model.UserID, System.Data.DbType.Int32);
            }
            if (model.CompanyID > -1)
            {
                sb.Append(" AND T.CompanyID=@CompanyID");
                dparams.Add("CompanyID", model.CompanyID, System.Data.DbType.Int32);
            }
            if (!string.IsNullOrWhiteSpace(model.BCreateDate))
            {
                DateTime bDate = EConvert.ToDateTime(model.BCreateDate);
                if (bDate.Year > 1900)
                {
                    sb.Append(" AND T.CreatedDate>@BCreateDate");
                    dparams.Add("BCreateDate", bDate.AddDays(-1).Date, System.Data.DbType.DateTime);
                }
            }
            if (!string.IsNullOrWhiteSpace(model.ECreateDate))
            {
                DateTime eDate = EConvert.ToDateTime(model.ECreateDate);
                if (eDate.Year > 1900)
                {
                    sb.Append(" AND T.CreatedDate<@ECreateDate");
                    dparams.Add("ECreateDate", eDate.AddDays(1).Date, System.Data.DbType.DateTime);
                }
            }
            if (model.ClueType > -1)
            {
                sb.Append(" AND T.ClueType=@ClueType");
                dparams.Add("ClueType", model.ClueType, System.Data.DbType.Int32);
            }

            if (!string.IsNullOrWhiteSpace(model.PlateNo))
            {
                sb.Append(" AND charindex(@PlateNo,T.PlateNo)>0");
                dparams.Add("PlateNo", model.PlateNo, System.Data.DbType.String);
            }
            if (!string.IsNullOrWhiteSpace(model.Brand))
            {
                sb.Append(" AND charindex(@Brand,T.Brand)>0");
                dparams.Add("Brand", model.Brand, System.Data.DbType.String);
            }
            if (!string.IsNullOrWhiteSpace(model.CModel))
            {
                sb.Append(" AND charindex(@Model,T.Model)>0");
                dparams.Add("Model", model.CModel, System.Data.DbType.String);
            }

            sb.Append(" ORDER BY SortNum DESC");
        }

        /// <summary>
        /// 构建查询条件
        /// </summary>
        /// <param name="model"></param>
        /// <param name="sb"></param>
        /// <param name="dparams"></param>
        private void AddParams(ClueSearchModel model, StringBuilder sb, DynamicParameters dparams)
        {
            if (model.UserID > -1)
            {
                sb.Append(" AND T.UserID=@UserID");
                dparams.Add("UserID", model.UserID, System.Data.DbType.Int32);
            }
           
            if (model.BrandID > -1)
            {
                sb.Append(" AND T.BrandID=@BrandID");
                dparams.Add("BrandID", model.BrandID, System.Data.DbType.Int32);
            }
            else if (model.ModelID > -1)
            {
                sb.Append(" AND T.ModelID=@ModelID");
                dparams.Add("ModelID", model.ModelID, System.Data.DbType.Int32);
            }

            if (model.BeginDate.Year > 1900)
            {
                sb.Append(" AND T.EndDate>@BeginDate");
                dparams.Add("BeginDate", model.BeginDate.AddDays(-1).Date, System.Data.DbType.DateTime);
            }

            if (model.EndDate.Year > 1900)
            {
                sb.Append(" AND T.EndDate<@EndDate");
                dparams.Add("EndDate", model.EndDate.AddDays(1).Date, System.Data.DbType.DateTime);
            }
            
            if (model.FlowStep > -1)
            {
                sb.Append(" AND T.FlowStep=@FlowStep");
                dparams.Add("FlowStep", model.FlowStep, System.Data.DbType.Int32);
            }

            if (model.PurchaseStep > -1)
            {
                sb.Append(" AND T.PurchaseStep=@PurchaseStep");
                dparams.Add("PurchaseStep", model.PurchaseStep, System.Data.DbType.Int32);
            }

            if (model.ClueType > -1)
            {
                sb.Append(" AND T.ClueType=@ClueType");
                dparams.Add("ClueType", model.ClueType, System.Data.DbType.Int32);
            }
            
            if (!string.IsNullOrWhiteSpace(model.PlateNo))
            {
                sb.Append(" AND charindex(@PlateNo,T.PlateNo)>0");
                dparams.Add("PlateNo", model.PlateNo, System.Data.DbType.String);
            }
           
            sb.Append(" ORDER BY SortNum DESC");
        }

        /// <summary>
        /// 获取首页统计
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public List<int> GetStatistic(long userId, DateTime dt)
        {
            string sql = @" SELECT COUNT(ClueID) AS CT FROM Clues WITH(NOLOCK) WHERE UserID=@UserID AND AssignFrt=@AssignFrt
                            UNION ALL
                            SELECT COUNT(ClueID) AS CT FROM Clues WITH(NOLOCK) WHERE UserID=@UserID AND AssignFrt=@AssignFrt AND FlowStep=0";
            return dbContext.Connection.Query<int>(sql, new { UserID = userId, AssignFrt = dt.ToString("yyyyMMdd") }).AsList();
        }

        /// <summary>
        /// 分配线索
        /// </summary>
        /// <param name="model"></param>
        public void AssignUser(AssignUserModel model)
        {
            var clue = Single(model.ClueID);
            clue.Assign = DateTime.Now;
            clue.AssignFrt = clue.Assign.ToString("yyyyMMdd");
            clue.UserID = model.UserID;

            //更新用户名称
            var usr = dbContext.Connection.Get<Users>(model.UserID);
            Update(clue);
        }

        /// <summary>
        /// 分配线索
        /// </summary>
        /// <param name="model"></param>
        public void AssignUsers(AssignUsersModel model)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var item in model.ClueIDs)
            {
                sb.Append("," + item);
            }

            //更新用户名称
            var usr = dbContext.Connection.Get<Users>(model.UserID);

            string sql = "SELECT * FROM Clues WHERE ClueID IN ({0})";
            var clues = Query(string.Format(sql, sb.Remove(0, 1).ToString()));
            foreach (var clue in clues)
            {
                clue.Assign = DateTime.Now;
                clue.AssignFrt = clue.Assign.ToString("yyyyMMdd");
                clue.UserID = model.UserID;
                Update(clue);
            }

        }

        /// <summary>
        /// 更新已经同步
        /// </summary>
        /// <param name="clueId"></param>
        /// <returns></returns>
        public int UpdateMessage(int clueId)
        {
            string sql = "UPDATE Clues SET IsMessage=1 WHERE ClueID=" + clueId;
            return Execute(sql);
        }
    }
}